package com.congiya.youtube.db;

//import java.util.ArrayList;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class SqlLiteDBUtil {
	
	SQLiteDatabase db;
	
	private static String LOG_TAG = "SqlLiteDBUtil";
	
		
	public SqlLiteDBUtil() {
		
		Log.i(LOG_TAG, "called SqlLiteDBUtil");
		
		openDatabase();		
	}

	private void openDatabase() {
		
		try {
			db = SQLiteDatabase.openOrCreateDatabase(
					"/data/data/com.congiya.youtube/mydata", null);
					//, SQLiteDatabase.CREATE_IF_NECESSARY);
			
			Log.i(LOG_TAG, "openDatabase");
		} catch (Exception e) {
			Log.e(LOG_TAG, e.toString());
		}
	}
	
	public void insertDB(int depth, int parentSeq, String title) {
		db.beginTransaction();
		String sql = "";
		try {		
/*
 
 			drop table tb_menu;
 				 
 			create table tb_menu
 				(seq integer primary key autoincrement, title text, depth integer, parentSeq integer);
 				
 			
 				1. 이클립스 프로젝트 인코딩 UTF-8로 변경, CMD 창의 엔코딩 UTF-8로 변경
 				
 				cmd> chcp 65001 (949:한글, 65001:utf-8)
			
			
 				
 			insert into tb_menu(title, depth, parentSeq) values ('영화', 1, 0); 	
 			
 			insert into tb_menu(title, depth, parentSeq) values ('드라마', 1, 0);	
 				
 				 			
 			insert into tb_menu(title, depth, parentSeq) values ('코미디' 2, 1); 	
 				 			
 			insert into tb_menu(title, depth, parentSeq) values ('S/F', 2, 1); 	 
 			 			
 			
 			insert into tb_menu(title, depth, parentSeq) values ('드라마 (MBC)', 2, 2); 
 				 			
 			insert into tb_menu(title, depth, parentSeq) values ('드라마 (SBS)', 2, 2); 	
 			
 			insert into tb_menu(title, depth, parentSeq) values ('액션', 2, 1); 
 			
 			insert into tb_menu(title, depth, parentSeq) values ('depth1-2', 1, 0);
 			
 			insert into tb_menu(title, depth, parentSeq) values ('depth2-1' 2, 1); 	
 			
 			insert into tb_menu(title, depth, parentSeq) values ('depth2-2' 2, 1); 	
 */
			

			sql = "insert into tb_menu(title, depth, parentSeq) values ('" 
					+ title +"', " + depth +", " + parentSeq + ")";
			
			db.execSQL(sql);
			
		} catch(Exception e) {
			Log.e(LOG_TAG, e.toString());
		} finally {
			db.endTransaction();
		}
	}

	public String[] getTopMenuList() {
		
		return new String[]{"영화", "드라마"};
		
	}
/*	
	public ArrayList<String[]> getTopMenuList() {
		
		String[] columns = {"seq", "title", "count" };
		String[] titleList;
		String[] subCntList;
		ArrayList<String[]> list = new ArrayList<String[]>();
		StringBuffer buff = new StringBuffer();
		
   		buff
			.append("select m1.seq seq, m1.title title, count(m2.seq) count ") 
			.append("from tb_menu m1, tb_menu m2 ")
			.append("where m1.seq = m2.parentSeq and m1.depth = 1 ")
			.append("group by m2.parentSeq ")
	   		.append("order by m1.seq asc ");
   		
   		Cursor c = db.rawQuery(buff.toString(), null);
   		
//		Cursor c = db.query("tb_menu", columns, 
//		selection, null, null, null, "title");
   		
		int totalCnt = c.getCount();
		int idxTitle = c.getColumnIndex("title");
		int idxCount = c.getColumnIndex("count");
		int idxListCnt = 0;
		
		Log.i(LOG_TAG, "totalCnt  : " + totalCnt + " : " + columns.length);
		
		titleList = new String[totalCnt];
		subCntList = new String[totalCnt];
		
		while(c.moveToNext()) {
			
			titleList[idxListCnt] = c.getString(idxTitle) + "("+c.getInt(idxCount)+" 건)";
			subCntList[idxListCnt] = String.valueOf(c.getInt(idxCount));
			idxListCnt++;
		}
			
		list.add(titleList);
		list.add(subCntList);
		
//		Log.i(LOG_TAG, "getTopMenuList.size : " + menuList.length);
		
		return list;
		
	}
	
*/	
/*	
	public Vector getSubMenuList(int depth, int parentSize) {
		
		String[] columns = {"seq", "title", "depth", "parentSeq" };
		
		String selection = "depth=" + depth;
		
		Cursor c = db.query("tb_menu", columns, 
				selection, null, null, null, "parentSeq");
						
		int totalCnt = c.getCount();
		int idxTitle = c.getColumnIndex("title");
		int idxParentSeq = c.getColumnIndex("parentSeq");
		int parentMenuCnt = 0;
		int subMenuCnt = 0;
		int prevParentSeq = 0;
//		Vector vList = new Vector();
//		Vector vMeuList = new Vector();
		
		String[][] menuList = new String[parentSize][totalCnt];

		Log.i(LOG_TAG, "menuList.totalCnt:" + totalCnt + ", parentSize:" + parentSize);
		
		while(c.moveToNext()) {
			
			menuList[parentMenuCnt][subMenuCnt] = c.getString(idxTitle);
//			vList.add(c.getString(idxTitle));
			
			if(prevParentSeq != c.getInt(idxParentSeq) 
					&& prevParentSeq != 0) {
				parentMenuCnt++;
				subMenuCnt = 0;	
//				vMeuList.add(vList);
//				vList = new Vector();
			}
						
			Log.i(LOG_TAG, "getSubMenuList[" + parentMenuCnt +"][" + subMenuCnt +"]" + c.getString(idxTitle));
			
			prevParentSeq = c.getInt(idxParentSeq);
			subMenuCnt++;
		}
		
		return vMeuList;
		
	}
	
*/
	
	public String[][] getSubMenuList(int depth, int parentSize) {
		
		String[] columns = {"seq", "title", "depth", "parentSeq" };
		
		String selection = "depth=" + depth;
		
		Cursor c = db.query("tb_menu", columns, 
				selection, null, null, null, "parentSeq");
						
		int totalCnt = c.getCount();
		int idxTitle = c.getColumnIndex("title");
		int idxParentSeq = c.getColumnIndex("parentSeq");
		int parentMenuCnt = 0;
		int subMenuCnt = 0;
		int prevParentSeq = 0;
		
		String[][] menuList = new String[parentSize][totalCnt];
		
//		Log.i(LOG_TAG, "menuList.totalCnt:" + totalCnt + ", parentSize:" + parentSize);
		
		while(c.moveToNext()) {
			
			if(prevParentSeq != c.getInt(idxParentSeq) 
					&& prevParentSeq != 0) {
				parentMenuCnt++;
				subMenuCnt = 0;				
			}
			
			menuList[parentMenuCnt][subMenuCnt] = c.getString(idxTitle);
//			Log.i(LOG_TAG, "getSubMenuList[" + parentMenuCnt +"][" + subMenuCnt +"]" + c.getString(idxTitle));
//						
			prevParentSeq = c.getInt(idxParentSeq);
			subMenuCnt++;
		}
		
		return menuList;
		
	}
	
/*	public String[][] getMenuList(int depth) {
		
		String[] columns = {"seq", "title", "depth", "parentSeq" };
		String[][] menuList;
		String selection = "depth=" + depth;

		
		Cursor c = db.query("tb_menu", columns, 
				selection, null, null, null, "title");
		
		int totalCnt = c.getCount();
		int idxSeq = c.getColumnIndex("seq");
		int idxTitle = c.getColumnIndex("title");
		int idxDepth = c.getColumnIndex("depth");
		int idxParentSeq = c.getColumnIndex("parentSeq");
		int idxListCnt = 0;
		
		Log.i(LOG_TAG, "menuList  : " + totalCnt + " : " + columns.length);
		
		menuList = new String[totalCnt][columns.length];
		
		while(c.moveToNext()) {
			
			menuList[idxListCnt][0] = Integer.toString(c.getInt(idxSeq));
			menuList[idxListCnt][1] = c.getString(idxTitle);
			menuList[idxListCnt][2] = Integer.toString(c.getInt(idxDepth));
			menuList[idxListCnt][3] = Integer.toString(c.getInt(idxParentSeq));
			 
			Log.i(LOG_TAG, idxListCnt + " : " + menuList[idxListCnt][0] + " : " + menuList[idxListCnt][1]);
			
			idxListCnt++;
		}
			
		Log.i(LOG_TAG, "depth : " + depth +", menuList.size : " + menuList.length);
		
		return menuList;
		
	}*/
	
	

}
